package com.br.framework.excel;

import java.io.InputStream;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFName;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartRequest;

import com.br.framework.annotation.EcExcel;
import com.br.framework.excel.impl.DefaultDataCellStyle;
import com.br.framework.excel.impl.DefaultHeaderCellStyle;
import com.br.framework.excel.impl.DefaultTitleCellStyle;
import com.br.framework.excel.inter.DataCellStyle;
import com.br.framework.excel.inter.ExcelConvert;
import com.br.framework.excel.inter.HeaderCellStyle;
import com.br.framework.excel.inter.TitleCellStyle;
import com.br.framework.exception.BaseException;
import com.br.framework.util.lang.DateUtil;
import com.br.framework.util.lang.StringUtil;


public class ExcelUtil {

	private static Logger logger = LoggerFactory.getLogger(ExcelUtil.class);
	private static String DEFAULT_DATE_FORMAT = "yyyy.MM.dd";
	/**
	 * 엑셀의 다운로드명을 가지고 온다.
	 * @param workbook
	 * @return
	 */
	public static String getDownloadFileName(HSSFWorkbook workbook){
		String fileName = null;
		if(workbook.getNumberOfNames() < 1)
		{
			return null;
		} else
		{
			fileName = workbook.getNameName(0);
			return (new StringBuilder()).append(fileName).append(".xls").toString();
		}
	}

	/**
	 * workbook를 생성한다.
	 * @param excelModel
	 * @return
	 */
	public static HSSFWorkbook createWorkbook(ExcelModel excelModel){
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFName hssfName = wb.createName();
		//파일명
		String fileName = excelModel.getFileName();
		//시트명
		String sheetName = excelModel.getSheetName();

		if(StringUtil.isEmpty(fileName)){
			fileName = "excel";
		}
		if(StringUtil.isEmpty(sheetName)){
			fileName = "sheet";
		}
		hssfName.setNameName(fileName);
		wb.createSheet(sheetName);
		return wb;
	}

	/**
	 * 타이틀을 설정한다.
	 * @param wb
	 * @param sheetIdx
	 * @param titlePosition
	 * @param title
	 * @param titleCellStyle
	 */
	public static void  createTitle(HSSFWorkbook wb
									,int sheetIdx
									,int titlePosition[]
									,String title
									,TitleCellStyle titleCellStyle){
		if(titleCellStyle==null){
			titleCellStyle = new DefaultTitleCellStyle();
		}
		int rowIdx = titlePosition[0];
		int cellIdx = titlePosition[1];
		HSSFRow titleRow = wb.getSheetAt(sheetIdx).createRow(rowIdx);
        HSSFCell titleCell = titleRow.createCell((short) cellIdx);
        titleCell.setCellType(HSSFCell.CELL_TYPE_STRING);
        titleCell.setCellValue(new HSSFRichTextString(title));
        titleCellStyle.titleCellStyle(wb, titleCell);
	}

	/**
	 * 헤더영역을 그린다.
	 * @param wb
	 * @param sheetIdx
	 * @param dataPosition
	 * @param dataIds
	 * @param excelData
	 * @param headerCellStyle
	 */
	public static void createHeader(HSSFWorkbook wb
									,int sheetIdx
									,int dataPosition[]
									,String dataIds[]
									,Object excelData
									,HeaderCellStyle headerCellStyle){
		if(headerCellStyle==null){
			headerCellStyle = new DefaultHeaderCellStyle();
		}
		int rowIdx = dataPosition[0];
		int cellIdx = dataPosition[1];
		HSSFRow titleRow = wb.getSheetAt(sheetIdx).createRow(rowIdx);
		Map<String,String> excelHeader = decribeHeader(dataIds,excelData);
		for(String dataId : dataIds){
			String headerTitle = excelHeader.get(dataId);
			HSSFCell headerCell = titleRow.createCell((short) cellIdx);
			headerCell.setCellType(HSSFCell.CELL_TYPE_STRING);
			headerCell.setCellValue(new HSSFRichTextString(headerTitle));
			headerCellStyle.headerCellStyle(wb, headerCell);
			cellIdx++;
		}
	}

	/**
	 * 데이타목록을 그린다.
	 * @param wb
	 * @param sheetIdx
	 * @param dataPosition
	 * @param dataIds
	 * @param excelDataList
	 * @param convert
	 * @param dataCellStyle
	 */
	public static void createDataList(HSSFWorkbook wb
									,int sheetIdx
									,int dataPosition[]
									,String dataIds[]
									,List<Object> excelDataList
									,ExcelConvert convert
									,DataCellStyle dataCellStyle){
		if(excelDataList==null || excelDataList.size()==0){
			return;
		}
		if(dataCellStyle==null){
			dataCellStyle = new DefaultDataCellStyle();
		}
		int rowIdx = dataPosition[0]+1;
		int initCellIdx = dataPosition[1];
		for(int i=0;i<excelDataList.size();i++){
			HSSFRow titleRow = wb.getSheetAt(sheetIdx).createRow(rowIdx);
			Object data = excelDataList.get(i);
			Map<String,String> excelData = decribeData(dataIds,data,convert);
			int cellIdx = initCellIdx;
			for(String dataId : dataIds){
				String value = excelData.get(dataId);
				HSSFCell dataCell = titleRow.createCell((short) cellIdx);
				dataCell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
				dataCell.setCellValue(new HSSFRichTextString(value));
				dataCellStyle.dataCellStyle(wb, dataCell);
				cellIdx++;
			}
			rowIdx++;
		}
	}


	/**
	 * 해더를 가지고 온다.
	 * @param dataIds
	 * @param excelData
	 * @return
	 */
	public static Map<String,String> decribeHeader(String[] dataIds,Object excelData){
		Field fields[] = excelData.getClass().getDeclaredFields();
		Map<String,String> map = new HashMap<String,String>();
		for(Field field : fields){
			EcExcel anntotation = field.getAnnotation(EcExcel.class);
			if(anntotation==null){
				continue;
			}
			String dataId = StringUtil.isEmpty(anntotation.dataId())?field.getName():anntotation.dataId();
			String header = anntotation.header();
			if(StringUtil.matchArray(dataIds,dataId)){
				map.put(dataId, header);
			}
		}
		return map;
	}

	/**
	 * Devkim Excel Annotation을 선언한 객체를 Map으로 describe 한다.
	 * @param dataids
	 * @param excelData
	 * @param convert
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public static Map<String,String> decribeData(String[] dataIds,Object excelData,ExcelConvert convertClass){
		@SuppressWarnings("rawtypes")
		Class clazz = excelData.getClass();
		Field fields[] = clazz.getDeclaredFields();
		Map<String,String> map = new HashMap<String,String>();
		for(Field field : fields){
			EcExcel anntotation = field.getAnnotation(EcExcel.class);
			if(anntotation==null){
				continue;
			}
//			header , format , prefix , suffix , convert
			String format = anntotation.format();
			String prefix = anntotation.prefix();
			String suffix = anntotation.suffix();
			String convert = anntotation.convert();
			String fieldNm = field.getName();
			String dataId = StringUtil.isEmpty(anntotation.dataId())?fieldNm:anntotation.dataId();

			if(StringUtil.matchArray(dataIds,dataId)){
				String getter = "get"+fieldNm.substring(0, 1).toUpperCase() + fieldNm.substring(1);
				try{
//					값을 가지고 온다.
					Object objValue = clazz.getMethod(getter).invoke(excelData);
					objValue = objValue==null?"":objValue;
					String strValue = String.valueOf(objValue);
//					포맷을 확인한다.
					if(StringUtil.isNotEmpty(format)){
						strValue = format(format,strValue);
//					convert를 확인한다.
					}else if(StringUtil.isNotEmpty(convert)
							&& convertClass!=null
					){
						strValue = convertClass.convert(convert, strValue);
					}
//					prefix가 있는지 확인한다.
					if(StringUtil.isNotEmpty(prefix)){
						StringBuffer sb = new StringBuffer(prefix).append(strValue);
						strValue = sb.toString();
					}
//					suffix가 있는지 확인한다.
					if(StringUtil.isNotEmpty(suffix)){
						StringBuffer sb = new StringBuffer(strValue).append(suffix);
						strValue = sb.toString();
					}
					map.put(dataId,strValue);
				}catch(Exception e){
					logger.debug(e.getMessage());
				}
			}
		}
		return map;
	}

	/**
	 * @param format
	 * date^yyyy.MM.dd  integer[3자리컴마]  float[3자리컴마]
	 * date옵션 = 구분자^yyyy.MM.dd
	 * @param val
	 * @return
	 */
	public static String format(String format,Object val){
		String value = String.valueOf(val);
		if(format.startsWith("date")){
			String vals[] = StringUtil.explode(format, "^");
			String formatter = DEFAULT_DATE_FORMAT;
			if(vals.length>1){
				formatter = vals[1];
			}
			value = DateUtil.dateFormat(value, formatter);
		}else if(format.startsWith("integer")){
			value = StringUtil.format(val);
		}else if(format.startsWith("float")){
			value = StringUtil.format(val);
		}
		return value;
	}

	/**
	 * 엑셀파일을 읽어서 맵을 반환한다.
	 * @param request
	 * @param matchMap
	 * @return
	 * @throws DevkimBaseException
	 */
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public static List upload(HttpServletRequest request,Map<String,String> matchMap,Class resultClass) throws BaseException,Exception{
		if(!(request instanceof MultipartRequest)){
			throw new  BaseException("common.excel.nofile");
		}
		MultipartRequest multipartRequest = (MultipartRequest)request;
		Map<String, MultipartFile>  fileMap = multipartRequest.getFileMap();
		InputStream inp = null;
		for(MultipartFile file:fileMap.values()){
			String originalFileName = StringUtil.substringAfterLast(file.getOriginalFilename(),".");
			if(StringUtil.matchArray(new String[]{"xls","xlsx"}, originalFileName)){
//				String uid = UUID.randomUUID().toString();
//					File f = file.transferTo(file);
				inp = file.getInputStream();
				break;
			}
		}
//			파일이 없습니다.
		if(inp==null){
			throw new  BaseException("common.excel.notExcelFile");
		}
		HSSFWorkbook wb = new HSSFWorkbook(inp);
//			헤더정보가 없습니다.
		if(matchMap==null){
			throw new  BaseException("common.excel.noheader");
		}

		int columnSize = matchMap.size();

//			컬럼위치와 필드명을 저장한다.
		String[][] dataTable= new String[columnSize][2];

//			헤더타이틀확인용으로 저장한다.
		String[] titles = new String[columnSize];
		String[] titleKeys = new String[columnSize];
		int idx = 0;
		Iterator<String> keySet = matchMap.keySet().iterator();
		while(keySet.hasNext()){
			String key=keySet.next();
			titles[idx] = matchMap.get(key);
			titleKeys[idx] = key;
			idx++;
		}

		HSSFSheet sheet = wb.getSheetAt(0);
		int last = sheet.getLastRowNum();
		int dataRowIdx = 0;
//			헤더확인
		for(int rIdx=0;rIdx<last;rIdx++){
			HSSFRow row = sheet.getRow(rIdx);
			if(row==null){
				continue;
			}
			int lastCellNum = row.getLastCellNum();
			if(lastCellNum==-1){
				continue;
			}
			int checkCount = 0;
			for(int cIdx=0;cIdx<lastCellNum; cIdx++){
				HSSFCell cel = row.getCell((short) cIdx);
				if(cel==null){
					continue;
				}
				String value = cellValueToString(cel);
				int macthIdx = StringUtil.matchArrayIdx(titles, value);
				if(macthIdx>=0){
					dataTable[checkCount][0]=String.valueOf(cIdx);
					dataTable[checkCount][1]=titleKeys[macthIdx];
					checkCount++;
				}
			}
//				헤더위치 확인완료.
			if(checkCount>=(columnSize/2)){
				dataRowIdx = rIdx+1;
				break;
			}
		}

//		데이타를 가지고 온다.
		List resultList = new ArrayList();
		for(int rIdx=dataRowIdx;rIdx<last;rIdx++){
			HSSFRow row = sheet.getRow(rIdx);
			if(row==null){
				continue;
			}
			int lastCellNum = row.getLastCellNum();
			if(lastCellNum==-1){
				continue;
			}
			Map resultMap = new HashMap();
			for(String[] dataMap : dataTable){
				int cellIdx = 0;
				try{
					cellIdx = Integer.parseInt(dataMap[0]);
				}catch(NumberFormatException nfe){
					continue;
				}
				String dataId = dataMap[1];
				if(cellIdx>lastCellNum){
					continue;
				}
				HSSFCell cel = row.getCell((short) cellIdx);
				String value = cellValueToString(cel);
				resultMap.put(dataId, value);
			}
			Object obj = resultClass.newInstance();
			BeanUtils.populate(obj, resultMap);
			resultList.add(obj);
		}
		return resultList;
	}

	/**
	 * cel값을 가지고 온다.
	 * @param cel
	 * @return
	 */
	public static String cellValueToString(HSSFCell cel){
		try{
			int cellType = cel.getCellType();
			String rst = "";
			switch (cellType) {
			case 0: //CELL_TYPE_NUMERIC
				double val = cel.getNumericCellValue();
				String tmp = String.valueOf(val);
				if(StringUtil.isEquals("0",StringUtil.substringAfterLast(tmp, "."))){
					rst = String.valueOf((long)val);
				}else{
					rst = tmp;
				}
				break;
			case 1: //CELL_TYPE_STRING
				rst = cel.getRichStringCellValue().toString();
				break;
			case 2: //CELL_TYPE_FORMULA
				rst = cel.getCellFormula();
				break;
			case 3:	//CELL_TYPE_BLANK
				rst = cel.getRichStringCellValue().toString();
				break;
			case 4:	//CELL_TYPE_BOOLEAN
				rst = String.valueOf(cel.getBooleanCellValue());
				break;
			case 5:	//CELL_TYPE_ERROR
				rst = String.valueOf(cel.getErrorCellValue());
				break;
			default:
				break;
			}
			return rst;
		}catch(Exception e){
			return "";
		}
	}
}
